﻿using System;
using System.Data;
using System.Text;
using DGen;
using System.Collections.Generic;

namespace Tab2Sql
{


















    internal partial class DataTable2SQL
    {
        // PRIVATE FIELDS
        // ----------------------------------------------------------------------------
        /// <summary>
        /// Most important variable in this class.
        /// </summary>
        private StringBuilder EndS;
            
        private const string myGap = " ";
        private const string myTab = "    ";
        private  DataTable ourDataTable;
        private int rowCount;
        private SqlStatementType sqlMode;

        

        public Err ErrorLog;
        public int StatusCode = 0;
        public Tab2SqlObj MyObject;
        public Tab2sqlOptions MyOptions;
        private SQLVariants sqlVariant;

        private bool headerDone = false;
        private bool logTableAlreadyCreated = false;
        private int batchCount = 1;

        //----------------------------------------------------------------------------------------------
        public DataTable2SQL(DataTable dt, SQLVariants sqlVar)
        {
            MyOptions = new Tab2sqlOptions();
            initiateDT2SQL(dt, sqlVar);
            
        }
        public DataTable2SQL(DataTable dt)
        {
            MyOptions = new Tab2sqlOptions();
            sqlVariant = MyOptions.SqlVariant;
            initiateDT2SQL(dt, MyOptions.SqlVariant);
        }
        public DataTable2SQL(DataTable dt, Tab2sqlOptions options)
        {
   
            MyOptions = options;
            sqlVariant = MyOptions.SqlVariant;
            initiateDT2SQL(dt, MyOptions.SqlVariant);

        }

        public DataTable2SQL(DataTable dt, SqlStatementType moo, Tab2sqlOptions options)
        {
            MyObject = new Tab2SqlObj();
            sqlMode = SqlStatementType.DataMode;
            if (moo == SqlStatementType.DataMode)
            {
                theRtb = new System.Windows.Forms.RichTextBox();
            }
            MyOptions = options;
            ourDataTable = dt;
            GetSQL();
        }
        public DataTable2SQL(DataTable dt, Dictionary<string, DataTypes> columnTypes,Tab2sqlOptions options)
        {

            sqlMode = SqlStatementType.MultiInsert;
            sqlVariant= options.SqlVariant;
                  ourDataTable = dt;
                  EndS = new StringBuilder();
                  ErrorLog = new Err("DataTable2SQL");
            MyObject = new Tab2SqlObj();
             columnTypes.Values.CopyTo(MyObject.ColumnTypes,0);
             GenerateSQLStatement();
        }
    
   

    

        //----------------------------------------------------------------------------------------------
        private void initiateDT2SQL(DataTable dt, SQLVariants sqlVar)
        {
            
            sqlVariant = sqlVar;
            sqlMode = SqlStatementType.Standard;
         
            
            ourDataTable = dt;
            rowCount = dt.Rows.Count;
            EndS = new StringBuilder(rowCount * 100);
            if (sqlMode != SqlStatementType.IntoExisting)
            {
                if (rowCount > Properties.Settings.Default.InsertLimit)
                {
                    MyOptions.SqlStatementMode = SqlStatementType.MultiInsert;
                    sqlMode = MyOptions.SqlStatementMode;
                }
                if (MyOptions.SqlStatementMode== SqlStatementType.MultiInsert)
                {
                    //confusingly, Single Statement Mode = Multi Insert. 
                    //multi insert simply means more than one row at a time. 
                    sqlMode = SqlStatementType.MultiInsert;
                }
            }
            ErrorLog = new Err("DataTable2SQL");
        }



        // CONSTANTS
        // ----------------------------------------------------------------------------


        // PUBLIC METHODS
        // ----------------------------------------------------------------------------
        /// <summary>
        /// Produces SQL statement from given datatable.
        /// </summary>
        /// <param name="dt"></param>
        private void GenerateSQLStatement()
        {
            if (headerDone == false)
            {
                generateTableStatement();
                generateInserts();
            }

        }

        /// <summary>
        /// Produces SQL Statement in the form INSERT INTO foobar SELECT x FROM y UNION ALL SELECT x2 from y2 UNION ALL...etc
        /// </summary>
        /// <param name="dt"></param>
        private void GenerateSQLStatementSingleInsert()
        {
            if (headerDone == false)
            {
                generateTableStatement();
                generateSelect();
            }
        }

        public void GetSQL()
        {

            switch (sqlMode)
            {
                case SqlStatementType.Standard:
                    GenerateSQLStatement();
                    break;
                case SqlStatementType.MultiInsert:
                    GenerateSQLStatementSingleInsert();
                    break;
                case SqlStatementType.IntoExisting:
                    GenerateSQLStatementIntoExisting();
                    break;
                case SqlStatementType.DataMode:
                    GenerateDataOutpupt();
                    break;
            }

            switch (sqlVariant)
            {
                case SQLVariants.SQLite:
                    EndS = sqlTranslate.RemoveTSQL(EndS);
                    break;
                case SQLVariants.TSql:
                    //
                    break;
                default:
                    //
                    break;
            }

        }

        private void GenerateDataOutpupt()
        {
            

                MyObject = new Tab2SqlObj();
                var dtHelp = new dtHelper(ourDataTable);
                MyObject.ColumnTypes = dtHelp.ColTypes;
                MyObject.LongestLine = dtHelp.LongestLines;
                dtHelp = null;


            generateDataOutput();
            
        }

        private void GenerateSQLStatementIntoExisting()
        {
            headerDone = true;
            generateSelect();
        }

        // PRIVATE METHODS
        // ----------------------------------------------------------------------------
        private void generateTableStatement()
        {
            if(headerDone==true)
            {
                return;
                throw new Exception("We've already done this!");
            }

            headerDone = true;

            if (MyOptions == null)
            {
                MyOptions = new Tab2sqlOptions();
            }
            if (MyObject == null)
            {
                MyObject = new Tab2SqlObj();
                var dtHelp = new dtHelper(ourDataTable);
                MyObject.ColumnTypes = dtHelp.ColTypes;
                MyObject.LongestLine = dtHelp.LongestLines;
                dtHelp = null;
            }

            if (MyOptions.TableName == null || MyOptions.TableName == String.Empty)
            {
                MyOptions.TableName = "#foo";
            }
            if (ourDataTable.Rows.Count == 0)
            {
                // "No rows found!";
            }
            else
            {
                if (EndS == null)
                {
                    EndS = new StringBuilder(ourDataTable.Rows.Count * 1000);
                }
                doHeader();
                EndS.Append("CREATE TABLE ");
                EndS.Append(MyOptions.TableName);
                EndS.Append(Environment.NewLine + "(\t\t");
                if (MyOptions.IncludeRowNumber) // then output "[RNo] INTEGER"
                {
                    EndS.Append(Environment.NewLine + myTab + myTab);
                    // TODO : Harcoded value. Should be setting. 11/10/2009 10:34:46
                    int fixedLen = 45; //indentation
                    string rowNoName = "RNo";
                    if (ourDataTable.Columns.Contains(rowNoName) || ourDataTable.Columns.Contains("["+rowNoName+"]"))
                    {
                        rowNoName = getNewNameForRowNoColumn();
                    }

                    EndS.Append("["+rowNoName+"]" + DGen.DText.Replicate(myGap, fixedLen) + "INTEGER,");
                   
                }
                int i = 0; //column number
                foreach (DataColumn dc in ourDataTable.Columns)
                {
                    string columnName = dc.ToString();
                    if (columnName.Length > 0)
                    {
                        //TODO : Hardcoded value.  11/10/2009 10:34:46
                        int columnLen = Math.Max(50 - columnName.Length, 2);
                        EndS.Append(Environment.NewLine + myTab + myTab);
                        string columnNameWithBrackets = columnName;
                        if (!columnNameWithBrackets.Contains("[") && !columnNameWithBrackets.Contains("]"))
                        {
                            columnNameWithBrackets = "["+columnNameWithBrackets.Trim()+"]";
                        }
                        EndS.Append(columnNameWithBrackets);
                        EndS.Append(DGen.DText.Replicate(myGap, columnLen));

                        // datatype (e.g. VARCHAR, INTEGER, DATETIME, etc)
                        //--------------------------------------------------------
                        DataTypes tmpDataType = MyObject.ColumnTypes[i];
                        if (MyObject.ColumnTypes[i] == DataTypes.unknown)
                        {
                            tmpDataType = DataTypes.varchar;
                            MyObject.ColumnTypes[i] = DataTypes.varchar;
                        }
                        if (MyObject.ColumnTypes[i] == DataTypes.percentage)
                        {
                            tmpDataType = DataTypes.numeric;
                        }
                        EndS.Append(tmpDataType.ToString().ToUpper());

                        // scale -e.g. varchar(50)
                        if (MyObject.ColumnTypes[i] == DataTypes.varchar)
                        {
                            int LongestLine = Math.Max(MyObject.LongestLine[i] + 5, dc.MaxLength + 3);
                            if (LongestLine > 3999)
                            {
                                EndS.Append("(MAX)");
                            }
                            else
                            {
                                EndS.Append("(" + LongestLine + ")");
                            }
                        }
                        // numeric scale / precision - e.g. NUMERIC(14,7)
                        if (MyObject.ColumnTypes[i] == DataTypes.numeric)
                        {
                            string columnScale = getColumnScale(MyObject.Highest[i]);
                            EndS.Append(columnScale);
                        }
                        if (MyObject.ColumnTypes[i] == DataTypes.percentage)
                        {
                            string columnScale = "(9,5)";
                            EndS.Append(columnScale);
                        }
                        // next column...
                        EndS.Append(",");
                    }
                    i++;
                }
                doAdditionalColumns(myTab);
                EndS.Remove(EndS.Length - 1, 1);
                EndS.AppendLine(Environment.NewLine + myTab + ");");
            }
        }

        private string getNewNameForRowNoColumn()
        {
            string baseName = "rowNo";
            if (!ourDataTable.Columns.Contains(baseName))
            {
                return baseName;
            }
            int upperLimit = 10000;
            for (int i = 0; i < upperLimit; i++)
            {
                string combo = baseName + i.ToString();
                if (!ourDataTable.Columns.Contains(combo))
                {
                    return combo;
                }
            }
            return "FAIL";
        }


        // This is the "body" of the table.
        private void generateInserts()
        {
            MyObject.ColCount = ourDataTable.Columns.Count;
            int RowNo = 0;
            var x = new string[MyObject.ColCount];
            var prefix = new StringBuilder();
            prefix.AppendLine();
            prefix.Append("INSERT INTO ");
            prefix.AppendLine(MyOptions.TableName);
            prefix.Append(" VALUES\t (");
            foreach (DataRow r in ourDataTable.Rows)
            {
                RowNo++;
                EndS.Append(prefix);
                var dtype = new DataTypes[MyObject.ColCount];

                if (MyOptions.IncludeRowNumber)
                {
                    EndS.Append(RowNo + ",");
                }
                for (int i = 0; i < MyObject.ColCount; i++)
                {
                    try
                    {
                        x[i] = r.ItemArray.GetValue(i).ToString();
                        dtype[i] = MyObject.ColumnTypes[i];
                    }
                    catch (Exception ex)
                    {
                        ErrorLog.Add(ex.Message);
                    }
                }
                int iCount = 0;
                foreach (string column_value in x)
                {
                    string tmpHolder = column_value;
                    if (column_value == null || column_value.Length == 0)
                    {
                        tmpHolder = "null";
                        EndS.Append(tmpHolder + ",");
                    }
                    else
                    {
                        if (dtype[iCount] == DataTypes.percentage)
                        {
                            tmpHolder = tmpHolder.Replace("%", String.Empty);
                            try
                            {
                                Decimal q = Convert.ToDecimal(tmpHolder);
                                q = q/100;
                                tmpHolder = q.ToString();
                            }
                            catch (Exception ex)
                            {
                                ErrorLog.Add("Error converting a percentage value.");
                                ErrorLog.Add(ex.Message);
                            }
                        }
                        if (dtype[iCount] == DataTypes.numeric)
                        {
                            tmpHolder = DGen.DText.CleanNumeric(tmpHolder);
                        }
                        if (dtype[iCount] == DataTypes.datetime)
                        {
                            tmpHolder = DText.Convert2UKDate(tmpHolder);
                        }


                        if (tmpHolder.Contains("'"))
                        {
                            tmpHolder=DGen.DText.RemoveUnescapedApostrophes(tmpHolder);
                        }
                        EndS.Append("'" + tmpHolder + "',");
                    }
                    iCount++;
                }
                if (MyOptions.AddCol1Name.Length > 0)
                {
                    EndS.Append("null,");
                }
                if (MyOptions.AddCol2Name.Length > 0)
                {
                    EndS.Append("null,");
                }

                EndS.Remove(EndS.Length - 1, 1);

                EndS.AppendLine(");");
                //TODO : Hardcoded values. 11/10/2009 10:34:46
                if ((RowNo%1000) == 0)
                {
                    EndS.AppendLine("GO");
                }
            } // end of foreach

            EndS.AppendLine();
            var m = MyOptions.IncludeQueryFooter;
            doFooter();
        }

        

        


        //TODO : Refactor this. 
        private void generateSelect()
        {
            if (MyOptions.RecordBatchProfiles == true)
            {
                if (!logTableAlreadyCreated && EndS.ToString().Contains("CREATE TABLE #temp154") == false)
                {
                    writeCreateTemp154Table();
                }
            }

            var Batches = new StringBuilder[MyOptions.BatchSize];


            string globalPrefix = getGlobalPrefix();
            string suffix = "  " + Environment.NewLine + "UNION ALL ";
            string prefix = Environment.NewLine;
            prefix += "SELECT ";

            MyObject.ColCount = ourDataTable.Columns.Count;


     
            var x = new string[MyObject.ColCount];


            int RowNo = 0; int BatchRowNumber = 0; int BatchNumber = 0;
            foreach (DataRow r in ourDataTable.Rows)
            {
                var dtype = new DataTypes[MyObject.ColCount];
                if (BatchRowNumber == 0)
                {
                    if (BatchNumber > 0)
                    {
                        EndS.AppendLine(";");
                        EndS.AppendLine("COMMIT");
                    }
                    BatchNumber++;
                    string asterisks =DGen.DText.Replicate("*",40);
                    EndS.AppendLine("/*"+asterisks+" BATCH " + BatchNumber + " STARTING "+asterisks+"*/");
                    if (MyOptions.RecordBatchProfiles == true)
                    {
                        EndS.AppendLine("INSERT INTO #temp154 (BatchNo, tName) VALUES (" + BatchNumber + ", '" + MyOptions.TableName + "')" + Environment.NewLine +
                                        "GO");
                    }
                    else
                    {
                        EndS.AppendLine("GO");
                    }
                    EndS.Append(globalPrefix);

                    BatchRowNumber = MyOptions.BatchSize; // this detemines the batchsize
                }
                RowNo++;
                BatchRowNumber--;

                EndS.Append(prefix);

                if (MyOptions.IncludeRowNumber)
                {
                  
                    EndS.Append(RowNo + ",");
                }
                for (int i = 0; i < MyObject.ColCount; i++)
                {
                    try
                    {
                        x[i] = r.ItemArray.GetValue(i).ToString();
                    }
                    catch (Exception ex)
                    {
                        ErrorLog.Add(ex.Message);
                    }
                }
                int iCol = 0;
                foreach (string column_value in x)
                {
                    dtype[iCol] = MyObject.ColumnTypes[iCol];
                    string tmpHolder = column_value;

                    if (column_value != null)
                    {
                        if (column_value.Length == 0)
                        {
                            tmpHolder = "null";
                        }
                    }
                    if (column_value == null)
                    {
                        tmpHolder = "null";
                    }
               
                    if (tmpHolder.Contains("'"))
                    {
                        tmpHolder = DGen.DText.RemoveUnescapedApostrophes(tmpHolder);
                    }
                    if (dtype[iCol] == DataTypes.datetime)
                    {
                        tmpHolder = DText.Convert2UKDate(tmpHolder);
                    }
                    if (tmpHolder != "null")
                    {
                        EndS.Append("'" + tmpHolder + "',");
                    }
                    else
                    {
                        EndS.Append("" + tmpHolder + ",");
                   }
                    
                    iCol++;
                }
                if (MyOptions.AddCol1Name.Length > 0)
                {
           
                    EndS.Append("null,");
                }
                if (MyOptions.AddCol2Name.Length > 0)
                {
        
                    EndS.Append("null,");
                }
                EndS.Remove(EndS.Length - 1, 1);  // to get rid of now extraneous comma

                if (RowNo < ourDataTable.Rows.Count && BatchRowNumber > 0)
                {
                    EndS.Append(suffix);

                }
            } // end of foreach

            EndS.AppendLine(";" + Environment.NewLine +"COMMIT" + Environment.NewLine + "GO" +
                            Environment.NewLine);
            if (MyOptions.FolderMode == false && MyOptions.SuppressBatchStuff==false && MyOptions.RecordBatchProfiles==true)
            {
                EndS.AppendLine("SELECT * FROM #temp154");
            }
         
                batchCount = BatchNumber;    
            
            
            doFooter();
        }

        private string getGlobalPrefix()
        {
            string globalPrefix = "";
            if (sqlMode == SqlStatementType.MultiInsert)
            {
                globalPrefix = Environment.NewLine + "BEGIN TRANSACTION" + Environment.NewLine + "INSERT INTO ";
                globalPrefix += MyOptions.TableName;

            }
            if (sqlMode == SqlStatementType.IntoExisting)
            {
                IntoExisting iex = new IntoExisting(MyOptions.TableName);
                globalPrefix = "BEGIN TRANSACTION" +Environment.NewLine+ iex.InsertStatement;
            }
            return globalPrefix;
        }
        /// <summary>
        /// The "temp154" table is a profiling record of how fast each batch was at inserted. In multi-file inserts, repeatedly creating the same object can cause errors.
        /// </summary>
        private void writeCreateTemp154Table()
        {
            string a = "IF NOT EXISTS(SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME like '#temp154%') "+Environment.NewLine;
             a+= " BEGIN "+Environment.NewLine;
             a+= "CREATE TABLE #temp154 	( 	RowNo INT IDENTITY(1,1),	tName VARCHAR(255), BatchNo INT, 		DT DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP) )"+Environment.NewLine;
             a+= " END "+Environment.NewLine;

            EndS.AppendLine(a + Environment.NewLine);
            logTableAlreadyCreated = true;
        }
        public override string ToString()
        {
            if (ErrorLog.Status == 0)
            {
                applyTemplates();
                return EndS.ToString();
            }
            else
            {
                return ErrorLog.ToString();
            }
        }

    }
}


    
       



